﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SD.IDAL;
using SD.Entities;
using System.Data.SqlClient;
using System.Data;

namespace SD.DAL
{
    public class AddressDAO : BaseDAO, IAddressDAO
    {

        #region IAddressDAO Members

        /// <summary>
        /// Get all address
        /// </summary>
        /// <returns></returns>
        public List<Address> GetAll()
        {
            // Obtain SqlCommand object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure
            comm.CommandText = "AdrGetAll";

            // Execute stored procedure
            DataTable dt = ExecuteSelectCommand(comm);

            // Create list address objects
            List<Address> list = new List<Address>();

            // For each row in table address, add to list address
            foreach (DataRow row in dt.Rows)
            {
                Address a = new Address();
                a.AddressId = row["AddressID"].ToString();
                a.Line1 = row["Line1"].ToString();
                a.Line2 = row["Line2"] == DBNull.Value ? "" : row["Line2"].ToString();
                a.Line3 = row["Line3"] == DBNull.Value ? "" : row["Line3"].ToString();
                a.PostCode = row["PostCode"].ToString();
                a.Town = (row["TownID"] == DBNull.Value) ? null : (new TownDAO().GetById(row["TownID"].ToString()));
                a.County = (row["CountyID"] == DBNull.Value) ? null : (new CountyDAO().GetById(row["CountyID"].ToString()));
                a.Country = (row["CountryID"] == DBNull.Value) ? null : (new CountryDAO().GetById(row["CountryID"].ToString()));
                list.Add(a);
            }

            // return list address
            return list;
        }

        /// <summary>
        /// Get address by post code
        /// </summary>
        /// <param name="postCode"></param>
        /// <returns></returns>
        public List<Address> GetByPostCode(string postCode)
        {
            // Obtain SqlCommand object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure
            comm.CommandText = "AdrGetByPostCode";

            // Add parameters
            comm.Parameters.AddWithValue("@PostCode", postCode);

            // Execute stored procedure
            DataTable dt = ExecuteSelectCommand(comm);


            // Create list address objects
            List<Address> list = new List<Address>();

            // For each row in table address, add to list address
            foreach (DataRow row in dt.Rows)
            {
                Address a = new Address();
                a.AddressId = row["AddressID"].ToString();
                a.Line1 = row["Line1"].ToString();
                a.Line2 = row["Line2"] == DBNull.Value ? "" : row["Line2"].ToString();
                a.Line3 = row["Line3"] == DBNull.Value ? "" : row["Line3"].ToString();
                a.PostCode = row["PostCode"].ToString();
                a.Town = (row["TownID"] == DBNull.Value) ? null : (new TownDAO().GetById(row["TownID"].ToString()));
                a.County = (row["CountyID"] == DBNull.Value) ? null : (new CountyDAO().GetById(row["CountyID"].ToString()));
                a.Country = (row["CountryID"] == DBNull.Value) ? null : (new CountryDAO().GetById(row["CountryID"].ToString()));
                list.Add(a);
            }

            // return list
            return list;
        }

        /// <summary>
        /// Get address by id
        /// </summary>
        /// <param name="addressId"></param>
        /// <returns></returns>
        public Address GetById(string addressId)
        {
            // Obtain SqlCommand object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure
            comm.CommandText = "AdrGetById";

            // Add parameters
            comm.Parameters.AddWithValue("@AddressID", addressId);

            // Execute stored procedure
            DataTable dt = ExecuteSelectCommand(comm);

            // Create address object
            Address a = null;

            // If there is one result to be returned
            if (dt.Rows.Count > 0)
            {
                DataRow row = dt.Rows[0];
                a = new Address();
                a.AddressId = row["AddressID"].ToString();
                a.Line1 = row["Line1"].ToString();
                a.Line2 = row["Line2"] == DBNull.Value ? "" : row["Line2"].ToString();
                a.Line3 = row["Line3"] == DBNull.Value ? "" : row["Line3"].ToString();
                a.PostCode = row["PostCode"].ToString();

                a.Town = (row["TownID"] == DBNull.Value) ? null : (new TownDAO().GetById(row["TownID"].ToString()));
                a.County = (row["CountyID"] == DBNull.Value) ? null : (new CountyDAO().GetById(row["CountyID"].ToString()));
                a.Country = (row["CountryID"] == DBNull.Value) ? null : (new CountryDAO().GetById(row["CountryID"].ToString()));                
            }

            // Return address
            return a;
        }

        /// <summary>
        /// Insert new address
        /// </summary>
        /// <param name="address"></param>
        /// <returns></returns>
        public bool Insert(Address address)
        {
            // Obtain SqlCommand object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure
            comm.CommandText = "AdrInsert";

            // Add parameters
            comm.Parameters.AddWithValue("@Line1", address.Line1);
            comm.Parameters.AddWithValue("@Line2", address.Line2);
            comm.Parameters.AddWithValue("@Line3", address.Line3);
            comm.Parameters.AddWithValue("@PostCode", address.PostCode);
            comm.Parameters.AddWithValue("@TownID", address.Town.TownID);
            comm.Parameters.AddWithValue("@CountyID", address.County.CountyID);
            comm.Parameters.AddWithValue("@CountryID", address.Country.CountryID);

            int result = -1;
            try
            {
                // Execute stored procedure
                result = ExecuteNonQuery(comm);
            }
            catch (Exception ex)
            {
                throw ex;
            }

            // if insert successfully return true, otherwise return false
            return (result != -1);
        }

        /// <summary>
        /// Update address
        /// </summary>
        /// <param name="address"></param>
        /// <returns></returns>
        public bool Update(Address address)
        {
            return false;
        }

        /// <summary>
        /// Delete address
        /// </summary>
        /// <param name="addressId"></param>
        /// <returns></returns>
        public bool Delete(string addressId)
        {
            return false;
        }


        /// <summary>
        /// Get address by condition
        /// </summary>
        /// <param name="postcode"></param>
        /// <param name="street"></param>
        /// <param name="town"></param>
        /// <returns></returns>
        public List<Address> GetByCodition(string postcode, string street, string town)
        {
            // Obtain SqlCommand object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure
            comm.CommandText = "AdrGetCodition";

            // Add parameters
            comm.Parameters.AddWithValue("@postcode", postcode);
            comm.Parameters.AddWithValue("@street", street);
            comm.Parameters.AddWithValue("@town", town);

            // Execute stored procedure
            DataTable dt = ExecuteSelectCommand(comm);

            // Create new list address objects
            List<Address> list = new List<Address>();
            
            // For each row in table address, add to list address
            foreach (DataRow row in dt.Rows)
            {
                Address a = new Address();
                a.AddressId = row["AddressID"].ToString();
                a.PostCode = row["PostCode"].ToString();
                a.Line1 = row["Line1"].ToString();
                a.Line2 = row["Line2"] == DBNull.Value ? "" : row["Line2"].ToString();
                a.Line3 = row["Line3"] == DBNull.Value ? "" : row["Line3"].ToString();
                a.Town.Name = (row["Town"] == DBNull.Value) ? null : (row["Town"].ToString());
                a.County.Name = (row["County"] == DBNull.Value) ? null : (row["County"].ToString());
                a.Country.Name = (row["Country"] == DBNull.Value) ? null : (row["Country"].ToString());
                list.Add(a);
            }

            // Return list Address
            return list;
        }

        #endregion
    }
}
